USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenEntryList]    スクリプト日付: 11/08/2024 10:07:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenEntryList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultEkidenEntryList]
GO

CREATE PROCEDURE [dbo].[ResultEkidenEntryList]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
    @ROUNDNO int,
	@KUMINO int
AS
BEGIN

DECLARE @SQL nvarchar(max); 
DECLARE @PARA nvarchar(max); 
DECLARE @ROLLCALLFLG nvarchar(max);  

SET @SQL = '
SELECT @ROLLCALLFLG = KUM.ROLLCALLFLG
FROM TBL_KUMI AS KUM
WHERE KUM.TAIKAINO = '+ STR(@TAIKAINO)+'
AND KUM.KYOGINO = ' + STR(@KYOGINO)+'
AND KUM.SUBNO = ' + STR(@SUBNO)+'
AND KUM.ROUNDNO = ' + STR(@ROUNDNO)+'
AND KUM.KUMINO = ' + STR(@KUMINO)

SET @PARA = '@ROLLCALLFLG nvarchar(max) OUTPUT';

EXECUTE sp_executesql @SQL,@PARA, @ROLLCALLFLG OUTPUT;

DECLARE @SQL2 nvarchar(max); 
SET @SQL2 = '

SELECT KYO.KYOGISYANO
	,  KYO.KYOGISYAMEI AS TEAMMEI
	,  SYO.SYOZOKUMEI
	,  SYT.SYOZOKUTIMEI '

IF @ROLLCALLFLG = 1
	BEGIN
		SET @SQL2 = @SQL2 + ',  ORD.SOUJUN '
	END

SET @SQL2 = @SQL2 + '
	,  KYO2.KYOGISYAMEI
	,  KYO2.KYOGISYAKANA
	,  KYO2.KOJINSYOZOKUTIMEI
	,  dbo.Seibetu(KYO2.SEIBETU) AS SEIBETU
	,  KYO2.GAKUNEN
	,  KYO2.SEINEN
	,  SYO2.SYOZOKUMEI AS KOJINSYOZOKUMEI
	,  KUM.ROLLCALLFLG
FROM TBL_LANE AS LAN

LEFT JOIN TBL_KYOGISYA AS KYO
ON KYO.TAIKAINO = LAN.TAIKAINO
AND KYO.KYOGISYANO = LAN.KYOGISYANO

LEFT JOIN TBL_SYOZOKU AS SYO
ON SYO.TAIKAINO = LAN.TAIKAINO
AND SYO.SYOZOKUNO = KYO.SYOZOKUNO1

LEFT JOIN TBL_SYOZOKUTI AS SYT
ON SYT.SYOZOKUTINO = SYO.SYOZOKUTINO

LEFT JOIN TBL_KUMI AS KUM
ON KUM.TAIKAINO = LAN.TAIKAINO 
AND KUM.KYOGINO = LAN.KYOGINO
AND KUM.SUBNO = LAN.SUBNO
AND KUM.ROUNDNO = LAN.ROUNDNO
AND KUM.KUMINO = LAN.KUMINO

LEFT JOIN TBL_TEAM AS TEA
ON TEA.TAIKAINO = LAN.TAIKAINO
AND TEA.TEAMNO = KYO.KYOGISYANO

LEFT JOIN TBL_KYOGISYA AS KYO2
ON KYO2.TAIKAINO = LAN.TAIKAINO
AND KYO2.KYOGISYANO = TEA.KYOGISYANO
AND KYO2.SYOZOKUNO1 = KYO.SYOZOKUNO1

LEFT JOIN TBL_SYOZOKU AS SYO2
ON KYO2.TAIKAINO = SYO2.TAIKAINO
AND KYO2.SYOZOKUNO1 = SYO2.SYOZOKUNO '

IF @ROLLCALLFLG = 1
  BEGIN
	SET @SQL2 = @SQL2 + '
		LEFT JOIN TBL_ORDER AS ORD
		ON ORD.TAIKAINO = LAN.TAIKAINO 
		AND ORD.KYOGINO = LAN.KYOGINO
		AND ORD.SUBNO = LAN.SUBNO
		AND ORD.ROUNDNO = LAN.ROUNDNO
		AND ORD.KUMINO = LAN.KUMINO
		AND ORD.LANE = LAN.LANE
		AND ORD.KYOGISYANO = TEA.KYOGISYANO '
  END

SET @SQL2 = @SQL2 + '
	WHERE LAN.TAIKAINO = '+ STR(@TAIKAINO)+'
	AND LAN.KYOGINO = '+ STR(@KYOGINO)+ '
	AND LAN.SUBNO = '+ STR(@SUBNO)+ '
	AND LAN.ROUNDNO = '+ STR(@ROUNDNO)+ '
	AND LAN.KUMINO = '+ STR(@KUMINO)

IF @ROLLCALLFLG = 1
  BEGIN
	SET @SQL2 = @SQL2 + 'ORDER BY KYO.KYOGISYANO , ISNULL(ORD.SOUJUN,9999),KYO2.KYOGISYANO '
  END
ELSE
  BEGIN
	SET @SQL2 = @SQL2 + 'ORDER BY KYO.KYOGISYANO , TEA.ID '
  END

EXECUTE(@SQL2)

END